package com.navi.mould1.service.impl;

import com.navi.bean.in.ConditionBean;
import com.navi.bean.out.spc_chart_his_report.SpcChartHisReportGridOut;
import com.navi.bean.sql.SqlVo;
import com.navi.mould1.service.ISpcChartHisReportService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Service;

import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

import static com.navi.bean.sql.SqlVo.jointInSql;
import static com.navi.core.util.JdbcUtils.mySql_QueryRunner_QueryListBySql;


@Slf4j
@Service
public class SpcChartHisReportServiceImpl implements ISpcChartHisReportService {


    public List<SpcChartHisReportGridOut> getGridData(ConditionBean conditions) {
        /*** 控制线，中心线等数据查询 **/
        final SqlVo sqlVo1 = new SqlVo();
        sqlVo1.appendSql("SELECT");
        sqlVo1.appendSql(" B.chart_value, B.input_timestamp, A.chart_ucl, A.chart_usl, A.chart_target, A.chart_lsl, A.chart_lcl, A.evt_timestamp");
        sqlVo1.appendSql(" from icim_jn.spc_ret_chart_info_his A");
        sqlVo1.appendSql(" inner join spc_data.spc_ret_chart_data B");
        sqlVo1.appendSql(" on left(a.evt_timestamp, 10) = left( B.input_timestamp, 10 )");
        sqlVo1.appendSql(" and A.col_typ_fk = B.col_typ_fk");
        sqlVo1.appendSql(" and A.grp_no_fk = B.grp_no_fk");
        sqlVo1.appendSql(" and A.chart_no_fk = B.chart_no_fk");
        sqlVo1.appendSql(" and A.chart_typ_fk = B.chart_typ_fk");
        sqlVo1.appendSql("INNER JOIN (select col_typ_fk, grp_no_fk, chart_no, chart_name FROM icim_jn.spc_bis_chart_main) b_2 ON A.col_typ_fk = b_2.col_typ_fk AND A.grp_no_fk = b_2.grp_no_fk AND A.chart_no_fk = b_2.chart_no");
        sqlVo1.appendSql("WHERE 1=1");
        if (StringUtils.isNotEmpty(conditions.getControl_chart_grp())) {
            String grpNo_list_str = Arrays.stream(conditions.getControl_chart_grp().split(",")).map(data -> "'"+data.split("@")[0]+"'").distinct().collect(Collectors.joining(",", "(", ")"));
            String colType_list_str = Arrays.stream(conditions.getControl_chart_grp().split(",")).map(data -> "'"+data.split("@")[1]+"'").distinct().collect(Collectors.joining(",", "(", ")"));
            sqlVo1.appendSql("AND A.grp_no_fk in" + grpNo_list_str);
            sqlVo1.appendSql("AND A.col_typ_fk in" + colType_list_str);
        }
        if (StringUtils.isNotEmpty(conditions.getControl_chart())) {
            String chartNo_list_str = Arrays.stream(conditions.getControl_chart().split(",")).map(data -> "'"+data.split("@")[0]+"'").distinct().collect(Collectors.joining(",", "(", ")"));
            String chartTyp_list_str = Arrays.stream(conditions.getControl_chart().split(",")).map(data -> "'"+data.split("@")[1]+"'").distinct().collect(Collectors.joining(",", "(", ")"));
            String chartName_list_str = Arrays.stream(conditions.getControl_chart().split(",")).map(data -> "'"+data.split("@")[2]+"'").distinct().collect(Collectors.joining(",", "(", ")"));
            sqlVo1.appendSql("AND A.chart_no_fk in" + chartNo_list_str);
            sqlVo1.appendSql("AND A.chart_typ_fk in" + chartTyp_list_str);
            sqlVo1.appendSql("AND b_2.chart_name in" + chartName_list_str);
        }

        if (StringUtils.isNotEmpty(conditions.getFab_id())) sqlVo1.appendSql("AND B.fab_id_fk in" + jointInSql(conditions.getFab_id()));
        if (StringUtils.isNotEmpty(conditions.getBay_id())) sqlVo1.appendSql("AND B.line_id_fk in" + jointInSql(conditions.getBay_id()));
        if (StringUtils.isNotEmpty(conditions.getOpe_id())) sqlVo1.appendSql("AND B.p_ope_id_fk in" + jointInSql(conditions.getOpe_id()));
        if (StringUtils.isNotEmpty(conditions.getTool_main_id())) sqlVo1.appendSql("AND B.p_tool_id_fk in(select tool_id from bis_tool where root_tool_id in" + jointInSql(conditions.getTool_main_id()) + ")");
        if (StringUtils.isNotEmpty(conditions.getTool_sub_id())) sqlVo1.appendSql("AND B.p_tool_id_fk in" + jointInSql(conditions.getTool_sub_id()));
        if (StringUtils.isNotEmpty(conditions.getTool_id())) sqlVo1.appendSql("AND B.p_tool_id_fk in" + jointInSql(conditions.getTool_id()));
        if (StringUtils.isNotEmpty(conditions.getShift_id())) sqlVo1.appendSql("AND B.shift_id_fk in" + jointInSql(conditions.getShift_id()));
        if (StringUtils.isNotEmpty(conditions.getBoat_number())) sqlVo1.appendSql("AND B.boat_number in" + jointInSql(conditions.getBoat_number()));
        if (StringUtils.isNotEmpty(conditions.getStart_time())) sqlVo1.appendSqlAndAddParam("AND B.input_timestamp >= ? ", conditions.getStart_time());
        if (StringUtils.isNotEmpty(conditions.getEnd_time())) sqlVo1.appendSqlAndAddParam("AND B.input_timestamp <= ? ", conditions.getEnd_time());

        List<SpcChartHisReportGridOut> resultDataList = mySql_QueryRunner_QueryListBySql(sqlVo1, SpcChartHisReportGridOut.class);

        return resultDataList;
    }



}
